﻿
-- Create extended properties view
CREATE VIEW ExtProps.Properties
AS
SELECT class,
	class_desc,
	CASE class 
		WHEN 1 THEN 
			(
				SELECT SCHEMA_NAME(o.schema_id)
				FROM sys.objects o
				WHERE o.object_id = major_id
			) 
		END AS object_schema,
	CASE class 
		WHEN 0 THEN DB_NAME()
		WHEN 1 THEN OBJECT_NAME(major_id)
		WHEN 3 THEN SCHEMA_NAME(major_id)
		END AS object_name,
	CASE class
		WHEN 1 THEN
			(
				SELECT c.name
				FROM sys.columns c
				WHERE c.object_id = major_id
				AND c.column_id = minor_id
			)
		END AS column_name,
	major_id,
	minor_id,
	name,
	value
FROM sys.extended_properties;
GO

-- Create extended properties view instead of update trigger
CREATE TRIGGER ExtProps.Properties_Update
ON ExtProps.Properties
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	DECLARE @Class int,
		@Object_Schema sysname,
		@Object_Name sysname,
		@Column_Name sysname,		
		@Property_Name sysname,
		@Property_Value sql_variant,
		@Full_Name sysname;

	DECLARE Updated_Rows CURSOR
	FORWARD_ONLY READ_ONLY
	FOR SELECT class,
		object_schema,
		object_name,
		column_name,
		name,
		value
	FROM inserted;

	OPEN Updated_Rows;

	FETCH NEXT FROM Updated_Rows
	INTO @Class,
		@Object_Schema,
		@Object_Name,
		@Column_Name,
		@Property_Name,
		@Property_Value;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Class = 0
			EXECUTE ExtProps.PropUpdate NULL, @Property_Name, @Property_Value;	-- Database
		ELSE IF @Class = 3
			EXECUTE ExtProps.PropUpdate @Object_Name, @Property_Name, @Property_Value;	-- Schema
		ELSE IF @Class = 1 AND @Column_Name IS NULL
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '');
			EXECUTE ExtProps.PropUpdate @Full_Name, @Property_Name, @Property_Value;	-- Table/Proc/Function
		END ELSE
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '') + 
				COALESCE(N'.[' + @Column_Name + N']', '');
			EXECUTE ExtProps.PropUpdate @Full_Name, @Property_Name, @Property_Value; -- Column
		END;
		
		FETCH NEXT FROM Updated_Rows
		INTO @Class,
			@Object_Schema,
			@Object_Name,
			@Column_Name,
			@Property_Name,
			@Property_Value;
	END;
	CLOSE Updated_Rows;
	DEALLOCATE Updated_Rows;

END;
GO

-- Create extended proeprties view instead of delete trigger
CREATE TRIGGER ExtProps.Properties_Delete
ON ExtProps.Properties
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	DECLARE @Class int,
		@Object_Schema sysname,
		@Object_Name sysname,
		@Column_Name sysname,		
		@Property_Name sysname,
		@Property_Value sql_variant,
		@Full_Name sysname;

	DECLARE Deleted_Rows CURSOR
	FORWARD_ONLY READ_ONLY
	FOR SELECT class,
		object_schema,
		object_name,
		column_name,
		name,
		value
	FROM deleted;

	OPEN Deleted_Rows;

	FETCH NEXT FROM Deleted_Rows
	INTO @Class,
		@Object_Schema,
		@Object_Name,
		@Column_Name,
		@Property_Name,
		@Property_Value;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Class = 0
			EXECUTE ExtProps.PropDelete NULL, @Property_Name;	-- Database
		ELSE IF @Class = 3
			EXECUTE ExtProps.PropDelete @Object_Name, @Property_Name;	-- Schema
		ELSE IF @Class = 1 AND @Column_Name IS NULL
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '');
			EXECUTE ExtProps.PropDelete @Full_Name, @Property_Name;	-- Table/Proc/Function
		END ELSE
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '') + 
				COALESCE(N'.[' + @Column_Name + N']', '');
			EXECUTE ExtProps.PropDelete @Full_Name, @Property_Name; -- Column
		END;
		
		FETCH NEXT FROM Deleted_Rows
		INTO @Class,
			@Object_Schema,
			@Object_Name,
			@Column_Name,
			@Property_Name,
			@Property_Value;
	END;
	CLOSE Deleted_Rows;
	DEALLOCATE Deleted_Rows;

END;
GO

-- Create extended properties view instead of insert trigger
CREATE TRIGGER ExtProps.Properties_Insert
ON ExtProps.Properties
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	DECLARE @Object_Schema sysname,
		@Object_Name sysname,
		@Column_Name sysname,		
		@Property_Name sysname,
		@Property_Value sql_variant,
		@Full_Name sysname;

	DECLARE Inserted_Rows CURSOR
	FORWARD_ONLY READ_ONLY
	FOR SELECT object_schema,
		object_name,
		column_name,
		name,
		value
	FROM inserted;

	OPEN Inserted_Rows;

	FETCH NEXT FROM Inserted_Rows
	INTO @Object_Schema,
		@Object_Name,
		@Column_Name,
		@Property_Name,
		@Property_Value;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Object_Schema IS NULL AND @Object_Name IS NULL AND @Column_Name IS NULL
			EXECUTE ExtProps.PropInsert NULL, @Property_Name, @Property_Value;	-- Database
		ELSE IF @Object_Schema IS NULL AND @Column_Name IS NULL
			EXECUTE ExtProps.PropInsert @Object_Name, @Property_Name, @Property_Value;	-- Schema
		ELSE IF @Column_Name IS NULL
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '');
			EXECUTE ExtProps.PropInsert @Full_Name, @Property_Name, @Property_Value;	-- Table/Proc/Function
		END ELSE
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '') + 
				COALESCE(N'.[' + @Column_Name + N']', '');
			EXECUTE ExtProps.PropInsert @Full_Name, @Property_Name, @Property_Value; -- Column
		END;
		
		FETCH NEXT FROM Inserted_Rows
		INTO @Object_Schema,
			@Object_Name,
			@Column_Name,
			@Property_Name,
			@Property_Value;
	END;
	CLOSE Inserted_Rows;
	DEALLOCATE Inserted_Rows;

END;